package com.san.common.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

public class ExportExcelUtil {
	//显示的导出表的标题
		private String title;
		//导出表的列名
		private String[] rowName ;
		
		private List<Object[]>  dataList = new ArrayList<Object[]>();
		
		HttpServletResponse  response;
		
		//构造方法，传入要导出的数据
		public ExportExcelUtil(String title,String[] rowName,List<Object[]>  dataList){
			this.dataList = dataList;
			this.rowName = rowName;
			this.title = title;
		}
				
		/*
		 * 导出数据
		 * */
		public void export() throws Exception{
			try{
				HSSFWorkbook workbook = new HSSFWorkbook();						// 创建工作簿对象
				HSSFSheet sheet = workbook.createSheet(title);		 			// 创建工作表
				
				// 产生表格标题行
		        HSSFRow rowm = sheet.createRow(0);
		        HSSFCell cellTiltle = rowm.createCell(0);
		        
		        //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
		        HSSFCellStyle columnTopStyle = (HSSFCellStyle) this.getColumnTopStyle(workbook);//获取列头样式对象
		        HSSFCellStyle style = (HSSFCellStyle) this.getStyle(workbook);					//单元格样式对象
		        
		        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));  
		        cellTiltle.setCellStyle(columnTopStyle);
		        cellTiltle.setCellValue(title);
	    		 
				// 定义所需列数
				int columnNum = rowName.length;
				HSSFRow rowRowName = sheet.createRow(2);				// 在索引2的位置创建行(最顶端的行开始的第二行)
				
				// 将列头设置到sheet的单元格中
				for(int n=0;n<columnNum;n++){
					HSSFCell  cellRowName = rowRowName.createCell(n);				//创建列头对应个数的单元格
					cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);				//设置列头单元格的数据类型
	    			HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
	    			cellRowName.setCellValue(text);									//设置列头单元格的值
	    			cellRowName.setCellStyle(columnTopStyle);						//设置列头单元格样式
	    		}
				
	    		//将查询出的数据设置到sheet对应的单元格中
				for(int i=0;i<dataList.size();i++){
					
					Object[] obj = dataList.get(i);//遍历每个对象
					HSSFRow row = sheet.createRow(i+3);//创建所需的行数
					
					for(int j=0; j<obj.length; j++){
						HSSFCell  cell = null;   //设置单元格的数据类型
						/*if(j == 0){
							cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
							cell.setCellValue(i+1);	
						}else{*/
							cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
							if(!"".equals(obj[j]) && obj[j] != null){
								cell.setCellValue(obj[j].toString());						//设置单元格的值
							}
						/*}*/
						cell.setCellStyle(style);									//设置单元格样式
					}
				}
				//让列宽随着导出的列长自动适应
				for (int colNum = 0; colNum < columnNum; colNum++) {
		            int columnWidth = sheet.getColumnWidth(colNum) / 256;
		            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
		                HSSFRow currentRow;
		                //当前行未被使用过
		                if (sheet.getRow(rowNum) == null) {
		                    currentRow = sheet.createRow(rowNum);
		                } else {
		                    currentRow = sheet.getRow(rowNum);
		                }
		                if (currentRow.getCell(colNum) != null) {
		                    HSSFCell currentCell = currentRow.getCell(colNum);
		                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
		                        int length = currentCell.getStringCellValue().getBytes().length;
		                        if (columnWidth < length) {
		                            columnWidth = length;
		                        }
		                    }
		                }
		            }
		            if(colNum == 0){
		            	sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
		            }else{
		            	sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
		            }
		        }
				
				if(workbook !=null){
					response = getResponse();
					OutputStream out = response.getOutputStream();
					try
				    {
				        String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
				        String headStr = "attachment; filename=\"" + fileName + "\"";
				       
				        response.setContentType("APPLICATION/OCTET-STREAM");
				        response.setHeader("Content-Disposition", headStr);       
				        workbook.write(out);
				    }
				    catch (IOException e)
				    {
				        e.printStackTrace();
				    }
					finally {
				    	if(out != null){
				    		out.close();
				    	}
						if(workbook != null){
							workbook.close();
						}
					}
				}
	 
			}catch(Exception e){
				e.printStackTrace();
			}
			return;
		}
	 
	    /**
	     * 获取response
	     **/
	    private HttpServletResponse getResponse(){
	    	ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
	    	HttpServletResponse response = requestAttributes.getResponse();
			return response;
	    }
		
		/* 
		 * 列头单元格样式
		 */    
	  	public static CellStyle getColumnTopStyle(Workbook workbook) {
	  		
	  		  // 设置字体
	    	  Font font = workbook.createFont();
	    	  //设置字体大小
	    	  font.setFontHeightInPoints((short)11);
	    	  //字体加粗
	    	  font.setBold(true);
	    	  //设置字体名字 
	    	  font.setFontName("Courier New");
	    	  //设置样式; 
	    	  CellStyle style = workbook.createCellStyle();
	    	  //设置底边框; 
	    	  style.setBorderBottom(BorderStyle.THIN);
	    	  //设置底边框颜色;  
	    	  style.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex());
	    	  //设置左边框;   
	    	  style.setBorderLeft(BorderStyle.THIN);
	    	  //设置左边框颜色; 
	    	  style.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex());
	    	  //设置右边框; 
	    	  style.setBorderRight(BorderStyle.THIN);
	    	  //设置右边框颜色; 
	    	  style.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex());
	    	  //设置顶边框; 
	    	  style.setBorderTop(BorderStyle.THIN);
	    	  //设置顶边框颜色;  
	    	  style.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex());
	    	  //在样式用应用设置的字体;  
	    	  style.setFont(font);
	    	  //设置自动换行; 
	    	  style.setWrapText(false);
	    	  //设置水平对齐的样式为居中对齐;  
	    	  style.setAlignment(HorizontalAlignment.CENTER);
	    	  //设置垂直对齐的样式为居中对齐; 
	    	  style.setVerticalAlignment(VerticalAlignment.CENTER);
	    	  
	    	  return style;
	    	  
	  	}
	  	
	  	/*  
		 * 列数据信息单元格样式
		 */  
	  	public static CellStyle getStyle(Workbook workbook) {
		  	  // 设置字体
		  	  Font font = workbook.createFont();
		  	  //设置字体大小
		  	  //font.setFontHeightInPoints((short)10);
		  	  //字体加粗
		  	  //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		  	  //设置字体名字 
		  	  font.setFontName("Courier New");
		  	  //设置样式; 
		  	  CellStyle style = workbook.createCellStyle();
		  	  //设置底边框; 
		  	  style.setBorderBottom(BorderStyle.THIN);
		  	  //设置底边框颜色;  
		  	  style.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex());
		  	  //设置左边框;   
		  	  style.setBorderLeft(BorderStyle.THIN);
		  	  //设置左边框颜色; 
		  	  style.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex());
		  	  //设置右边框; 
		  	  style.setBorderRight(BorderStyle.THIN);
		  	  //设置右边框颜色; 
		  	  style.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex());
		  	  //设置顶边框; 
		  	  style.setBorderTop(BorderStyle.THIN);
		  	  //设置顶边框颜色;  
		  	  style.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex());
		  	  //在样式用应用设置的字体;  
		  	  style.setFont(font);
		  	  //设置自动换行; 
		  	  style.setWrapText(false);
		  	  //设置水平对齐的样式为居中对齐;  
		  	  style.setAlignment(HorizontalAlignment.CENTER);
		  	  //设置垂直对齐的样式为居中对齐; 
		  	  style.setVerticalAlignment(VerticalAlignment.CENTER);
		  	 
		  	  return style;
	  	
	  	}
}
